
package VLSys;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import DBConnection.*;
import java.sql.*;

import javax.swing.table.DefaultTableModel;

public class ReportsExpenses extends JPanel{
    JLabel expenseLog;
    JLabel information;
    JLabel fromDate;
    JLabel toDate;
    JLabel expenseType;
    
    DefaultTableModel expenseModel;
    
    JScrollPane expenseScroll;
    JTable expenseTable;
    
    JComboBox expenseTypes;
        
    ImageIcon notice_green = new ImageIcon("images/prompt_notice_green.png");
    ImageIcon ask_red = new ImageIcon("images/prompt_ask_red.png");
    ImageIcon notice_red = new ImageIcon("images/prompt_notice_red.png");

    String Months[] = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
    int[] intDay = new int[31];
    String[] strDay = new String[31];
    int[] intYear = new int[30];
    String strYear[] = new String[30];
    
    String expenseChoices[] = {"All", "Personal", "Operational"};
   
    JComboBox Month1 = new JComboBox(Months);
    JComboBox Day1 = new JComboBox();
    JComboBox Year1 = new JComboBox();
    
    JComboBox Month2 = new JComboBox(Months);
    JComboBox Day2 = new JComboBox();
    JComboBox Year2 = new JComboBox();
    
    JComboBox expenceChoice = new JComboBox(expenseChoices);
        
    JButton generate;
    
    
    
    public ReportsExpenses(final JFrame frame)
    {   setVisible(false);
        setLayout(null);
        setBackground(null);
        
        
        expenseLog = new JLabel("EXPENSE LOG");
        
        fromDate = new JLabel("From: ");
        toDate = new JLabel("To: ");
        expenseType = new JLabel("Expense Type: ");
        
        generate = new JButton();
        
        expenseLog.setFont(new Font("sansserif", Font.PLAIN, 40));
                
        fromDate.setBounds(121,50,150,20);
        
        Month1.setBounds(160,50,110,20);
        Day1.setBounds(270,50, 50, 20);
        Year1.setBounds(320,50, 80, 20);
        
        toDate.setBounds(135,75,150,20);
        
        Month2.setBounds(160,75,110,20);
        Day2.setBounds(270,75, 50, 20);
        Year2.setBounds(320,75, 80, 20);
        
        expenseType.setBounds(72,100,150,20);
        expenseLog.setBounds(10,10,300,30);
        
        expenceChoice.setBounds(160,100,150,20);
        
        generate.setText("Generate");
        generate.setBounds(160,125,100,20);
        
        InitializeDrop(new Integer(31) , "day1");
        InitializeDrop(new Integer(31) , "day2");
        InitializeYear();
        
        Month1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
                else if (Month1.getSelectedIndex() == 0 || Month1.getSelectedIndex() == 2 || Month1.getSelectedIndex() == 4 
                        || Month1.getSelectedIndex() == 6 || Month1.getSelectedIndex() == 7 || Month1.getSelectedIndex() == 9
                        || Month1.getSelectedIndex() == 11)
                    InitializeDrop(31, "day1");
                else if (Month1.getSelectedIndex() == 3 || Month1.getSelectedIndex() == 5 || Month1.getSelectedIndex() == 8
                        || Month1.getSelectedIndex() == 10)
                    InitializeDrop(30, "day1");
             }
         }
         );
          
        Month2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
                else if (Month2.getSelectedIndex() == 0 || Month2.getSelectedIndex() == 2 || Month2.getSelectedIndex() == 4 
                        || Month2.getSelectedIndex() == 6 || Month2.getSelectedIndex() == 7 || Month2.getSelectedIndex() == 9
                        || Month2.getSelectedIndex() == 11)
                    InitializeDrop(31, "day2");
                else if (Month2.getSelectedIndex() == 3 || Month2.getSelectedIndex() == 5 || Month2.getSelectedIndex() == 8
                        || Month2.getSelectedIndex() == 10)
                    InitializeDrop(30, "day2");                   
             }
         }
         );
          
        Year1.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year1.getSelectedItem().toString()));
                
                 if(Month1.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day1");
                    else
                        InitializeDrop(29, "day1"); 
                }
             }
         }
         );
          
        Year2.addActionListener(new ActionListener() {
             public void actionPerformed (ActionEvent e){
                 
                boolean leap = false;
                
                 leap = CheckLeap(Integer.parseInt(Year2.getSelectedItem().toString()));
                
                 if(Month2.getSelectedIndex() == 1)
                {
                    if(leap==false)
                        InitializeDrop(28, "day2");
                    else
                        InitializeDrop(29, "day2"); 
                }
             }
         }
         );
          
        generate.addActionListener(new ActionListener() {
            public void actionPerformed (ActionEvent e){
                 
            if(CheckDateRange() == false)
                {
                    Year1.setBackground(Color.YELLOW);
                    Month1.setBackground(Color.YELLOW);
                    Day1.setBackground(Color.YELLOW);
                    
                      JOptionPane.showMessageDialog(frame,
                                 "Invalid date range.", 
                                 "Vehicle Logging Service", 
                                 JOptionPane.ERROR_MESSAGE,
                                 notice_red);   
                 } 
             
            else
            {
                    Year1.setBackground(null);
                    Month1.setBackground(null);
                    Day1.setBackground(null);                    
                refreshTable();
                
            }
            }  
         }
         );
        
        expenceChoice.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {   refreshTable();
            }
        }
        );
        
        expenceChoice.addActionListener(new  ActionListener()
       {
            public void actionPerformed(ActionEvent e)
            {   refreshTable();
            }
        }
        );
        
        //table codes
          Object headers[] = {"Date", "Type", "Amount"};

          expenseTable = new JTable();
          expenseTable.setModel(new javax.swing.table.DefaultTableModel(null, headers){

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return false;
            }
            });
          
          expenseScroll = new JScrollPane();
          expenseTable.getTableHeader().setReorderingAllowed(false);
          expenseScroll.setViewportView(expenseTable);
          expenseScroll.setBounds(15, 150, 575, 250);
          expenseModel = (DefaultTableModel)expenseTable.getModel();
          expenseTable.getTableHeader().setResizingAllowed(false);
          
          //populateTable("SELECT * FROM expense ORDER BY 'expense_date'");
          getOldestLatestEntry();
          
        add(expenseLog);
        add(fromDate);
        add(toDate);
        add(expenseType);
        add(generate);
        add(Month1);
        add(Day1);
        add(Year1);
        add(Month2);
        add(Day2);
        add(Year2);
        add(expenceChoice);
        add(expenseScroll);
    }
    
    public void getOldestLatestEntry()
    {   String dates[] = new String[2];
        dates[0] = null;
        dates[1] = null;
        
        String oldestDate[] = new String[3];
        String latestDate[] = new String[3];
        String delimiter = "-";
        
        try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query to get latest and oldest Entry
                    PreparedStatement pstmt1 = conn.prepareStatement("SELECT MAX(expense_date) from expense ORDER BY expense_date");
                    PreparedStatement pstmt2 = conn.prepareStatement("SELECT MIN(expense_date) from expense ORDER BY expense_date");
                    
                    //SELECT MAX(`expense`.`expense_date`) FROM `expense` ORDER BY `expense`.`expense_date` ASC

                    //execute query
                    ResultSet rs1 = pstmt1.executeQuery();
                    ResultSet rs2 = pstmt2.executeQuery();
                    
                    while(rs1.next() && rs2.next())
                    {
                        dates[0] = (String)rs2.getString(1); //oldest entry
                        dates[1] = (String)rs1.getString(1); //latest entry
                    }
                                        
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                }    
        
        oldestDate = dates[0].split(delimiter);
        latestDate = dates[1].split(delimiter); 
        
        Year1.setSelectedItem(oldestDate[0]);
        Month1.setSelectedIndex(Integer.parseInt(oldestDate[1]) - 1);
        Day1.setSelectedIndex(Integer.parseInt(oldestDate[2]) - 1);
        
        Year2.setSelectedItem(latestDate[0]);
        Month2.setSelectedIndex(Integer.parseInt(latestDate[1]) - 1);
        Day2.setSelectedIndex(Integer.parseInt(latestDate[2]) - 1);
        
        refreshTable();
        //System.out.print(dates[0] + " " + dates[1]);
    }
    
    public String[] getSelectedMonth()
    {   String mon1 = new String();
        String mon2 = new String();
        String months[] = new String[2];
        
        if(Month1.getSelectedItem() == "January")
                    mon1 = "01";
                else if(Month1.getSelectedItem() == "Febuary")
                    mon1 = "02";
                else if(Month1.getSelectedItem() == "March")
                    mon1 = "03";
                else if(Month1.getSelectedItem() == "April")
                    mon1 = "04";
                else if(Month1.getSelectedItem() == "May")
                    mon1 = "05";
                else if(Month1.getSelectedItem() == "June")
                    mon1 = "06";
                else if(Month1.getSelectedItem() == "July")
                    mon1 = "07";
                else if(Month1.getSelectedItem() == "August")
                    mon1 = "08";
                else if(Month1.getSelectedItem() == "September")
                    mon1 = "09";
                else if(Month1.getSelectedItem() == "October")
                    mon1 = "10";
                else if(Month1.getSelectedItem() == "November")
                    mon1 = "11";
                else if(Month1.getSelectedItem() == "December")
                    mon1 = "12";
                
                if(Month2.getSelectedItem() == "January")
                    mon2 = "01";
                else if(Month2.getSelectedItem() == "Febuary")
                    mon2 = "02";
                else if(Month2.getSelectedItem() == "March")
                    mon2 = "03";
                else if(Month2.getSelectedItem() == "April")
                    mon2 = "04";
                else if(Month2.getSelectedItem() == "May")
                    mon2 = "05";
                else if(Month2.getSelectedItem() == "June")
                    mon2 = "06";
                else if(Month2.getSelectedItem() == "July")
                    mon2 = "07";
                else if(Month2.getSelectedItem() == "August")
                    mon2 = "08";
                else if(Month2.getSelectedItem() == "September")
                    mon2 = "09";
                else if(Month2.getSelectedItem() == "October")
                    mon2 = "10";
                else if(Month2.getSelectedItem() == "November")
                    mon2 = "11";
                else if(Month2.getSelectedItem() == "December")
                    mon2 = "12";
                
                months[0] = mon1;
                months[1] = mon2;
                return months;
    }
    
    public void populateTable(String pS){
         
              try{
                    //opens db connection
                    ConnectionFactory myFactory = ConnectionFactory.getFactory();
                    Connection conn = myFactory.getConnection();

                    //SQL Query
                    PreparedStatement pstmt = conn.prepareStatement(pS);

                    //execute query
                    ResultSet rs = pstmt.executeQuery();
                    
                    expenseModel.getDataVector().removeAllElements();
                    expenseTable.setAutoCreateRowSorter(true);

                    //populate JTable 
                    while(rs.next()){
                    expenseModel.addRow(new Object[]{rs.getString(2), rs.getString(3),rs.getString(4)});
                    expenseModel.fireTableDataChanged();
                    }
                    
                    //close DB connection
                    conn.close();
                }
                      
                catch(SQLException e)
                {
                   e.printStackTrace();
                   System.out.print("ayaw!");
                }
             
          }
    
    public void refreshTable(){
        String months[] = new String[2];
         String dates[] = new String[2];
         months = getSelectedMonth();
         
         dates[0] = Year1.getSelectedItem() +  "-" + months[0] + "-" + Day1.getSelectedItem();
         dates[1] = Year2.getSelectedItem() +  "-" + months[1] + "-" + Day2.getSelectedItem();
          
        
         if(expenceChoice.getSelectedItem() == "All")
                    populateTable("SELECT * FROM expense WHERE expense_date BETWEEN '" + dates[0] + "' AND '" + dates[1] + "' ORDER BY 'expense_date'");      
                else
                    populateTable("SELECT * FROM expense WHERE expense_date BETWEEN '" + dates[0] + "' AND '" + dates[1] + "' AND expense_type = '" + expenceChoice.getSelectedItem() + "' ORDER BY 'expense_date'");      
                
    }

    
    
    public void InitializeDrop(int ctr, String day) {
        if(day.equals("day1"))
        {
        Day1.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day1.addItem(strDay[a-1]);
            }
         Day1.repaint();
        }
        else if(day.equals("day2"))
        {
         Day2.removeAllItems();
        
         for(int a = 1; a <= ctr; a++ )
            {     
                intDay[a-1] = a;
                strDay[a-1] = Integer.toString(intDay[a-1]);
                Day2.addItem(strDay[a-1]);
            }
         Day2.repaint();
        }
            
    }
    
    public void InitializeYear() {
         for(int a = 2011; a <= 2031; a++)
            {     
                int index = 0;
                intYear[index] = a;
                strYear[index] = Integer.toString(intYear[index]);
                Year1.addItem(strYear[index]);
                Year2.addItem(strYear[index]);
                index++;
            }
    }
    
    public boolean CheckLeap(int year) {
        if(year%4 == 0)
        {
            if(year%100 != 0)
            {
                return true;
            }
            else
            {
                if(year%400 == 0)
                    return true;
                else
                    return false;
            }
        }
        return false;
        }
            
    public boolean CheckDateRange()
    {
        if((Year2.getSelectedIndex()- Year1.getSelectedIndex()) > 0)
        {
            Year1.setBackground(null);
            return true;
        }
        else if ((Year2.getSelectedIndex()- Year1.getSelectedIndex()) == 0){
            if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) > 0 )
            {
               return true;
            }
            else if((Month2.getSelectedIndex()- Month1.getSelectedIndex()) == 0) {
              if((Day2.getSelectedIndex()- Day1.getSelectedIndex()) >= 0)
                {
                    return true;
                }
            }
        }
        
         return false;  
    }    
    
    
    
}
